package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.StudentForData;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 * 不同类型，需要排除或参与的数据，如：人教版学员不参与率的计算，待转校学生 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-01-08
 */
public interface StudentForDataMapper extends BaseMapper<StudentForData> {

    @Select({"<script>",
            "SELECT",
                      "* ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t1.*,",
                        "t3.head_img,",
                        "t3.`name` AS student_name,",
                        "t4.`name` AS school_name,",
                        "t2.grade_id,",
                        "t2.grade_name,",
                        "t2.teacher_id,",
                        "t2.teacher_name",
                      "FROM",
                        "( SELECT * FROM student_for_data WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND lesson_type = ${lessonType} AND data_type_id = ${type} and deleted=0) AS t1",
                        "LEFT JOIN (",
                        "SELECT",
                          "student_grade.*,",
                          "grade.`name` AS grade_name,",
                          "grade.`teacher_id` AS teacher_id,",
                          "getStaffNameForId(teacher_id) as teacher_name",
                        "FROM",
                          "student_grade",
                          "LEFT JOIN grade ON student_grade.grade_id = grade.id ",
                        "WHERE",
                          "student_grade.year_part = ${yearPart} ",
                          "AND student_grade.quarter_num = ${quarterNum} ",
                          "AND student_grade.is_last = 1 ",
                          "AND student_grade.lesson_type = 1 ",
                          "AND student_grade.deleted = 0 ",
                        ") AS t2 ON t1.student_id = t2.student_id ",
                        "AND t1.subjects_id = t2.subjects_id",
                        "LEFT JOIN student AS t3 ON t1.student_id = t3.id",
                      "LEFT JOIN organization AS t4 ON t1.school_id = t4.id ",
                      ") AS t","where 1=1",
                        "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                            "and school_id =${schoolId}",
                        "</when>",
                        "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
                            "and year_part = ${yearPart}",
                        "</when>",
                        "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
                            "and `quarter_num` = ${quarterNum}",
                        "</when>",
                        "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                            "and subjects_id=${subjectsId}",
                        "</when>",
                        "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                            "and year_class_id=${yearClassId}",
                        "</when>",
                        "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
                            "and grade_id=${gradeId}",
                        "</when>",
                        "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
                            "and teacher_id=${teacherId}",
                        "</when>",
                        "<when test='studentName!=null and studentName!=&apos;&apos;'>",
                            "and student_name like '%${studentName}%'",
                        "</when>",
            "</script>"})
    public IPage<StudentForData> findAll(IPage<StudentForData> page, @Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("lessonType") Integer lessonType, @Param("type") Long type,@Param("yearClassId") Integer yearClassId,@Param("gradeId") Long gradeId,@Param("teacherId") Long teacherId, @Param("studentName") String studentName);


    @Select({"<script>",
            "SELECT",
                      "t1.id,",
                      "t1.school_id,",
                      "t1.student_id,",
                      "t1.subjects_id,",
                      "t1.year_part,",
                      "t1.quarter_num,",
                      "t1.year_class_id,",
                      "getSchoolName(t1.school_id) as school_name,",
                      "t2.grade_name,",
                      "t2.teacher_name,",
                      "t4.head_img,",
                      "t4.NAME AS student_name",
                    "FROM",
                      "(",
                      "SELECT",
                        "* ",
                      "FROM",
                        "order_detail ",
                      "WHERE",
                        "year_part = ${yearPart} ",
                        "AND quarter_num = ${quarterNum} ",
                        "AND lesson_type = ${lessonType}",
                        "AND deleted = 0 ",
                        "AND `status` &lt;&gt; 2 ",
                        "AND residue_course_count &gt; 0 ",
                      "GROUP BY",
                        "school_id,",
                        "subjects_id,",
                        "student_id ",
                      ") AS t1",
                      "INNER JOIN (",
                      "SELECT",
                        "student_grade.*,",
                        "grade.`name` AS grade_name,",
                        "staff.`name` as teacher_name",
                      "FROM",
                        "student_grade",
                        "LEFT JOIN grade ON student_grade.grade_id = grade.id ",
                        "LEFT JOIN staff ON grade.teacher_id = staff.id",
                      "WHERE",
                        "student_grade.year_part = ${yearPart} ",
                        "AND student_grade.quarter_num = ${quarterNum}",
                        "AND student_grade.lesson_type = 1 ",
                        "AND student_grade.deleted = 0 ",
                        "AND student_grade.`status` = 1 ",
                        "AND student_grade.is_last = 1 ",
                        "AND student_grade.lesson_type = ${lessonType} ",
                        "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                            " and student_grade.grade_id = #{gradeId}",
                        "</when>",
                        "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
                            " and grade.teacher_id = #{teacherId}",
                        "</when>",
                      ") AS t2 ON t1.student_id = t2.student_id AND t1.subjects_id = t2.subjects_id",
                      "INNER JOIN student AS t4 ON t1.student_id = t4.id",
                      "LEFT JOIN ( SELECT * FROM student_for_data WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND lesson_type = 1 AND deleted = 0 ) AS t3 ON t1.student_id = t3.student_id AND t1.subjects_id = t3.subjects_id",
                    "WHERE",
                      "t3.id IS NULL",
                    "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                        " and t1.school_id = #{schoolId}",
                    "</when>",
                    "<when test='studentId!=null and studentId!=&apos;&apos; '>",
                        " and t1.student_id = #{studentId}",
                    "</when>",
                    "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
                        " and t1.subjects_id = #{subjectsId}",
                    "</when>",
                    "<when test='yearPart!=null and yearPart!=&apos;&apos; '>",
                        " and t1.year_part = #{yearPart}",
                    "</when>",
                    "<when test='quarterNum!=null and quarterNum!=&apos;&apos; '>",
                        " and t1.quarter_num = #{quarterNum}",
                    "</when>",
                    "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
                        " and t2.grade_id = #{gradeId}",
                    "</when>",
                    "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                        " and t4.name like '%${studentName}%'",
                    "</when>",
            "</script>"})
    public IPage<StudentForData> getWaitTransterStudent(IPage<StudentForData> page, @Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("lessonType") Integer lessonType, @Param("type") Long type,@Param("yearClassId") Integer yearClassId,@Param("gradeId") Long gradeId,@Param("teacherId") Long teacherId, @Param("studentName") String studentName);
}
